package com.sopt.friends.util;

import java.io.InputStream;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import android.content.ContentResolver;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.net.Uri;
import android.provider.CallLog;
import android.provider.ContactsContract;
import android.provider.CallLog.Calls;
import android.text.format.DateUtils;
import android.util.Log;

import com.sopt.friends.R;
import com.sopt.friends.db.FriendsDBHelper;
import com.sopt.friends.db.Constants.FriendsDB;
import com.sopt.friends.statics.FriendsStaticVars;
import com.sopt.friends.vo.FriendsBasicMember;
import com.sopt.friends.vo.FriendsCommonItem;
import com.sopt.friends.vo.FriendsPhone;

public class FriendsContactsManager {
	private Context c;
	private ContentResolver currentResolver;
	private SQLiteDatabase db;

	public FriendsContactsManager(Context context,
			ContentResolver contentResolver) {
		this.c = context;
		this.currentResolver = contentResolver;
	}

	public SQLiteDatabase getDb() {
		return db;
	}

	public void setDb(SQLiteDatabase db) {
		this.db = db;
	}

	public ArrayList<String> setFriendsMemberUpdate() {
		FriendsDBHelper dbhelper = new FriendsDBHelper(c);

		int countNewMembers = 0;
		int countPhoneList = 0;
		int countUpdateMembers = 0;
		ArrayList<String> list = new ArrayList<String>();
		String[] columns = { FriendsDB.MEMBER_ID };

		db = dbhelper.getReadableDatabase();
		Cursor cursor = db.query(FriendsDB.TABLE_MEMBER, columns, null, null,
				null, null, FriendsDB.MEMBER_ID + " ASC");

		while (cursor.moveToNext()) {
			String curMemId = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_ID));
			list.add(curMemId);
		}
		cursor.close();
		db.close();

		db = dbhelper.getWritableDatabase();

		Cursor mainCursor = currentResolver.query(
				ContactsContract.Contacts.CONTENT_URI, null, null, null, null);

		while (mainCursor.moveToNext()) {
			String contactId = mainCursor.getString(mainCursor
					.getColumnIndex(ContactsContract.Contacts._ID));
			String photo_id = mainCursor.getString(mainCursor
					.getColumnIndex(ContactsContract.Contacts.PHOTO_ID));
			String displayName = mainCursor.getString(mainCursor
					.getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME));
			String hasPhone = mainCursor
					.getString(mainCursor
							.getColumnIndex(ContactsContract.Contacts.HAS_PHONE_NUMBER));
			if (list.size() > 0) {
				Boolean isExistMemberId = false;
				for (String curListId : list) {
					if (curListId.equals(contactId))
						isExistMemberId = true;
				}
				// Case : New Member
				if (!isExistMemberId) {
					ContentValues values = new ContentValues();

					values.put(FriendsDB.MEMBER_ID, contactId);
					values.put(FriendsDB.MEMBER_NAME, displayName);
					values.put(FriendsDB.MEMBER_REGDATE, new Timestamp(
							new Date().getTime()).toString());
					values.put(FriendsDB.GROUP_SEQ, "1");
					values.put(FriendsDB.MEMBER_PHOTO_ID, photo_id);
					values.put(FriendsDB.MEMBER_IS_DELETED, "0");
					Long result = db.insert(FriendsDB.TABLE_MEMBER, null,
							values);

					if (result > 0)
						countNewMembers++;

					if (hasPhone.equals("1")) {
						ArrayList<FriendsPhone> phoneList = insertFriendsPhoneList(
								contactId, true);
						countPhoneList += phoneList.size();
					}

					list.remove(contactId);
				} else {
					// Case : Exist Member
				}
			} else {
				ContentValues values = new ContentValues();

				values.put(FriendsDB.MEMBER_ID, contactId);
				values.put(FriendsDB.MEMBER_NAME, displayName);
				values.put(FriendsDB.MEMBER_REGDATE, new Timestamp(new Date()
						.getTime()).toString());
				values.put(FriendsDB.GROUP_SEQ, "1");
				values.put(FriendsDB.MEMBER_PHOTO_ID, photo_id);
				values.put(FriendsDB.MEMBER_IS_DELETED, "0");
				Long result = db.insert(FriendsDB.TABLE_MEMBER, null, values);

				if (result > 0)
					countNewMembers++;

				if (hasPhone.equals("1")) {
					ArrayList<FriendsPhone> phoneList = insertFriendsPhoneList(
							contactId, true);
					countPhoneList += phoneList.size();
				}
			}
		}
		mainCursor.close();
		db.close();

		if (list.size() > 0) {
			list.clear();
		}

		list.add(String.valueOf(countNewMembers));
		list.add(String.valueOf(countPhoneList));
		list.add(String.valueOf(countUpdateMembers));

		return list;
	}

	public ArrayList<FriendsBasicMember> getContactsList() {
		ArrayList<FriendsBasicMember> list = new ArrayList<FriendsBasicMember>();

		Cursor cursor = currentResolver.query(
				ContactsContract.Contacts.CONTENT_URI, null, null, null, null);
		while (cursor.moveToNext()) {

			FriendsBasicMember mFriendsBasicMember = new FriendsBasicMember();

			String contactId = cursor.getString(cursor
					.getColumnIndex(ContactsContract.Contacts._ID));
			String displayName = cursor.getString(cursor
					.getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME));
			String photo_id = cursor.getString(cursor
					.getColumnIndex(ContactsContract.Contacts.PHOTO_ID));

			mFriendsBasicMember.set_id(contactId);
			mFriendsBasicMember.setDisplayname(displayName);
			mFriendsBasicMember.setPhotoid(photo_id);

			Uri uri = ContentUris.withAppendedId(
					ContactsContract.Contacts.CONTENT_URI, Long
							.parseLong(contactId));
			InputStream is = ContactsContract.Contacts
					.openContactPhotoInputStream(currentResolver, uri);
			Bitmap bitmap = BitmapFactory.decodeStream(is);

			mFriendsBasicMember.setPhoto(bitmap);

			String hasPhone = cursor
					.getString(cursor
							.getColumnIndex(ContactsContract.Contacts.HAS_PHONE_NUMBER));

			if (hasPhone.equals("1")) {
				mFriendsBasicMember.setPhoneList(insertFriendsPhoneList(
						contactId, false));
			}

			Cursor emails = currentResolver.query(
					ContactsContract.CommonDataKinds.Email.CONTENT_URI, null,
					ContactsContract.CommonDataKinds.Email.CONTACT_ID + " = "
							+ contactId, null, null);
			int emailCount = 0;
			while (emails.moveToNext()) {
				// This would allow you get several email addresses
				String emailAddress = emails
						.getString(emails
								.getColumnIndex(ContactsContract.CommonDataKinds.Email.DATA));
				// Just using primary Email address
				if (emailCount == 0)
					mFriendsBasicMember.setEmail(emailAddress);
				emailCount++;
			}
			emails.close();

			list.add(mFriendsBasicMember);
		}
		cursor.close();

		return list;
	}

	private ArrayList<FriendsPhone> insertFriendsPhoneList(String contactId,
			Boolean doInsert) {
		ArrayList<FriendsPhone> phoneList = new ArrayList<FriendsPhone>();

		FriendsDBHelper dbhelper = new FriendsDBHelper(c);
		SQLiteDatabase sdb = dbhelper.getWritableDatabase();

		Cursor phones = currentResolver.query(
				ContactsContract.CommonDataKinds.Phone.CONTENT_URI, null,
				ContactsContract.CommonDataKinds.Phone.CONTACT_ID + " = "
						+ contactId, null, null);

		while (phones.moveToNext()) {
			
			String phoneNumber = phones
					.getString(phones
							.getColumnIndex(ContactsContract.CommonDataKinds.Phone.NUMBER));
			
			String phoneNumberType = "";
			if (phones
					.getColumnIndex(ContactsContract.CommonDataKinds.Phone.TYPE) > 0)
				phoneNumberType = phones
						.getString(phones
								.getColumnIndex(ContactsContract.CommonDataKinds.Phone.TYPE));

			FriendsPhone currentPhone = new FriendsPhone();
			currentPhone.setMemberid(contactId);
			currentPhone.setPhonenumber(phoneNumber);
			currentPhone.setPhonetype(phoneNumberType);

			ContentValues valuesPhone = new ContentValues();
			valuesPhone.put(FriendsDB.MEMBER_ID, contactId);
			valuesPhone.put(FriendsDB.PHONE_NUMBER, phoneNumber);
			valuesPhone.put(FriendsDB.PHONE_TYPE, phoneNumberType);

			if (doInsert) {
				Long phoneResult = sdb.insert(FriendsDB.TABLE_PHONE, null,
						valuesPhone);
				if (phoneResult > 0)
					phoneList.add(currentPhone);
			} else {
				phoneList.add(currentPhone);
			}
		}

		phones.close();
		sdb.close();

		return phoneList;

	}

	public int setInitDatabaseContactList(ArrayList<FriendsBasicMember> list) {
		int countMembers = 0;
		int countPhones = 0;

		FriendsDBHelper dbhelper = new FriendsDBHelper(c);

		SQLiteDatabase sdb = dbhelper.getWritableDatabase();

		for (FriendsBasicMember curMem : list) {
			ContentValues values = new ContentValues();
			values.put(FriendsDB.MEMBER_ID, curMem.get_id());
			values.put(FriendsDB.MEMBER_NAME, curMem.getDisplayname());
			values.put(FriendsDB.MEMBER_REGDATE, new Timestamp(new Date()
					.getTime()).toString());
			values.put(FriendsDB.GROUP_SEQ, "1");
			values.put(FriendsDB.MEMBER_PHOTO_ID, curMem.getPhotoid());
			values.put(FriendsDB.MEMBER_IS_DELETED, "0");
			Long result = sdb.insert(FriendsDB.TABLE_MEMBER, null, values);
			if (result > 0)
				countMembers++;
			
			
			if (curMem.getPhoneList()!=null && curMem.getPhoneList().size() > 0) {
				for (FriendsPhone curPhone : curMem.getPhoneList()) {
					ContentValues valuesPhone = new ContentValues();
					valuesPhone.put(FriendsDB.MEMBER_ID, curMem.get_id());
					valuesPhone.put(FriendsDB.PHONE_NUMBER, curPhone
							.getPhonenumber());
					valuesPhone.put(FriendsDB.PHONE_TYPE, curPhone
							.getPhonetype());
					Long phoneResult = sdb.insert(FriendsDB.TABLE_PHONE, null,
							valuesPhone);

					if (phoneResult > 0)
						countPhones++;
				}
			}
		}
		sdb.close();

		Log.v("changdoc", "CREATED " + countMembers + "PERSON" + "/ CREATED "
				+ countPhones + "PHONES.");
		return countMembers;
	}

	public ArrayList<String> getCallLogList() {
		ArrayList<String> list = new ArrayList<String>();

		Cursor cursor = currentResolver.query(CallLog.Calls.CONTENT_URI, null,
				null, null, Calls.DEFAULT_SORT_ORDER);
		while (cursor.moveToNext()) {

			String callstId = cursor
					.getString(cursor.getColumnIndex(Calls._ID));
			String number = cursor.getString(cursor
					.getColumnIndex(Calls.NUMBER));

			String date = cursor.getString(cursor.getColumnIndex(Calls.DATE));

			date = DateUtils.getRelativeDateTimeString(c, Long.parseLong(date),
					DateUtils.MINUTE_IN_MILLIS, DateUtils.WEEK_IN_MILLIS,
					DateUtils.FORMAT_UTC).toString();
			String type = cursor.getString(cursor.getColumnIndex(Calls.TYPE));

			list.add(callstId + number + date + type);
		}
		cursor.close();

		return list;
	}

	public ArrayList<FriendsCommonItem> getInfoMenuList() {
		ArrayList<FriendsCommonItem> list = new ArrayList<FriendsCommonItem>();

		FriendsDBHelper dbhelper = new FriendsDBHelper(c);
		db = dbhelper.getReadableDatabase();

		String[] colums = { "count(*) as count" };
		String where = "date(" + FriendsDB.MEMBER_REGDATE
				+ ") > date('now','-5 day') OR date("
				+ FriendsDB.MEMBER_REGDATE + ") = date('now')";
		int newCount = 0;
		Cursor cursor = db.query(FriendsDB.TABLE_MEMBER, colums, where, null,
				null, null, null);

		if (cursor.moveToNext()) {
			newCount = cursor.getInt(cursor.getColumnIndex("count"));
		}
		FriendsCommonItem fresh = new FriendsCommonItem();

		fresh.setTitle(c.getResources().getString(R.string.first_info_Fresh));
		fresh.setNewcount(newCount);
		fresh.setNextaction(FriendsStaticVars.ACTION_FIRST_TO_FRESH);

		where = "date(" + FriendsDB.MEMBER_BIRTHDAY
				+ ") > date('now','-2 day') OR date("
				+ FriendsDB.MEMBER_BIRTHDAY + ") < date('now','+2 day')";
		newCount = 0;
		cursor = db.query(FriendsDB.TABLE_MEMBER, colums, where, null, null,
				null, null);
		if (cursor.moveToNext()) {
			newCount = cursor.getInt(cursor.getColumnIndex("count"));
		}

		FriendsCommonItem birthday = new FriendsCommonItem();

		birthday.setTitle(c.getResources().getString(
				R.string.first_info_Birthday));
		birthday.setNewcount(newCount);
		birthday.setNextaction(FriendsStaticVars.ACTION_FIRST_TO_BIRTH);

		// TODO: ## Implement News
		where = "date(" + FriendsDB.MEMBER_BIRTHDAY
				+ ") > date('now','-3 day') OR date("
				+ FriendsDB.MEMBER_BIRTHDAY + ") = date('now','+2 day')";
		newCount = 0;
		cursor = db.query(FriendsDB.TABLE_MEMBER, colums, where, null, null,
				null, null);
		if (cursor.moveToNext()) {
			newCount = cursor.getInt(cursor.getColumnIndex("count"));
		}

		FriendsCommonItem news = new FriendsCommonItem();
		news.setTitle(c.getResources().getString(R.string.first_info_News));
		news.setNewcount(newCount);
		news.setNextaction(FriendsStaticVars.ACTION_FIRST_TO_NEWS);

		cursor.close();
		db.close();

		list.add(fresh);
		list.add(birthday);
		list.add(news);

		return list;
	}

	public ArrayList<FriendsCommonItem> getInfoByDateMenuList() {
		ArrayList<FriendsCommonItem> list = new ArrayList<FriendsCommonItem>();
		int bestCount = 0;
		int recentlyCount = 0;
		int forgottenCount = 0;
		
		ArrayList<callObj> recentlyCallList = new ArrayList<callObj>();
		ArrayList<String> callsList = new ArrayList<String>();
		ArrayList<callObj> callsNumberList = new ArrayList<callObj>();
		Cursor cursor = currentResolver.query(CallLog.Calls.CONTENT_URI, null,
				null, null, Calls.DEFAULT_SORT_ORDER);

		while (cursor.moveToNext()) {
			String date = cursor.getString(cursor.getColumnIndex(Calls.DATE));
			String number = cursor.getString(cursor
					.getColumnIndex(Calls.NUMBER));
			
			Date regDate = new Date(Long.parseLong(date));
			Date curDate = new Date();
			Long gapToToday = (curDate.getTime() - regDate.getTime())
					/ (1000 * 60 * 60 * 24);

			date = (String) DateUtils.getRelativeTimeSpanString(Long
					.parseLong(date));

			if (!callsList.contains(number)) {
				callsList.add(number);
				callsNumberList.add(new callObj(number, 0));
				// REcently
				if (gapToToday == 1 || gapToToday==0) {
					recentlyCallList.add(new callObj(number,0));
				}
			}

		}
		cursor.close();

		for(int i = recentlyCallList.size() - 1;i>-1;i--){
			callObj curObj = recentlyCallList.get(i);
			if(!isExistMemberByNumber(curObj.getNumber())){
				recentlyCallList.remove(i);
			}
		}

		String[] projection = { "count(" + Calls.NUMBER + ") as count" };
		for (callObj curNum : callsNumberList) {
			String selArg = Calls.NUMBER + " = ('" + curNum.getNumber() + "')";
			cursor = currentResolver.query(CallLog.Calls.CONTENT_URI,
					projection, selArg, null, null);
			while (cursor.moveToNext()) {
				int curCount = cursor.getInt(cursor.getColumnIndex("count"));
				curNum.setCount(curCount);
			}
			cursor.close();
		}

		for (int i = 0; i < callsNumberList.size() - 1; i++) {
			for (int j = i + 1; j < callsNumberList.size(); j++) {
				if (callsNumberList.get(i).getCount() < callsNumberList.get(j)
						.getCount()) {
					callObj curNum = new callObj(callsNumberList.get(j)
							.getNumber(), callsNumberList.get(j).getCount());
					callsNumberList.set(j, callsNumberList.get(i));
					callsNumberList.set(i, curNum);
				}
			}
		}

		ArrayList<callObj> existNumberList = new ArrayList<callObj>();

		FriendsDBHelper dbhelper = new FriendsDBHelper(c);
		db = dbhelper.getReadableDatabase();
		String[] columns = { FriendsDB.MEMBER_ID, FriendsDB.PHONE_NUMBER };
		String where = "";
		Cursor existCursor = db.query(FriendsDB.TABLE_PHONE, columns, where,
				null, null, null, null);
		
		while (existCursor.moveToNext()) {
			existNumberList.add(new callObj(existCursor.getString(existCursor
					.getColumnIndex(FriendsDB.PHONE_NUMBER)), 0));
		}
		existCursor.close();
		
		db.close();

		ArrayList<Integer> callIndexList = new ArrayList<Integer>();

		for (callObj curNum : callsNumberList) {
			if (curNum.getCount() > 0) {
				if (getMemberIdByNumber(curNum.getNumber()).length() > 0) {
					bestCount++;
					for (int i = 0; i < existNumberList.size(); i++) {
						if (curNum.getNumber().equals(
								existNumberList.get(i).getNumber())) {
							callIndexList.add(i);
						}
					}

				}
			}
		}

		for (int i = 0; i < callIndexList.size() - 1; i++)
			for (int j = i + 1; j < callIndexList.size(); j++) {
				if (callIndexList.get(i) < callIndexList.get(j)) {
					int cur = new Integer(callIndexList.get(j));
					callIndexList.set(j, callIndexList.get(i));
					callIndexList.set(i, cur);
				}
			}
		for (int i = existNumberList.size() - 1; i > -1; i--) {
			callObj cur = existNumberList.get(i);
			if (callIndexList.size() > 0 && callIndexList.get(0) == i) {
				callIndexList.remove(0);
				existNumberList.remove(cur);
			}
		}
		
		recentlyCount = recentlyCallList.size();
		forgottenCount = existNumberList.size();

		FriendsCommonItem best = new FriendsCommonItem();
		best.setTitle(c.getResources().getString(R.string.first_info_Best));
		best.setNewcount(bestCount);
		best.setNextaction(FriendsStaticVars.ACTION_FIRST_TO_BEST);

		FriendsCommonItem recently = new FriendsCommonItem();
		recently.setTitle(c.getResources().getString(
				R.string.first_info_Recently));
		recently.setNewcount(recentlyCount);
		recently.setNextaction(FriendsStaticVars.ACTION_FIRST_TO_RECENTLY);

		FriendsCommonItem forgotten = new FriendsCommonItem();
		forgotten.setTitle(c.getResources().getString(
				R.string.first_info_Forgotten));
		forgotten.setNewcount(forgottenCount);
		forgotten.setNextaction(FriendsStaticVars.ACTION_FIRST_TO_FORGOTTON);

		list.add(best);
		list.add(recently);
		list.add(forgotten);

		return list;
	}

	private class callObj {
		int count;
		String number;

		public callObj() {

		}

		public callObj(String number, int count) {
			this.number = number;
			this.count = count;
		}

		public int getCount() {
			return count;
		}

		public void setCount(int count) {
			this.count = count;
		}

		public String getNumber() {
			return number;
		}

		public void setNumber(String number) {
			this.number = number;
		}

	}

	public ArrayList<FriendsBasicMember> getFreshList(String curTab) {
		ArrayList<FriendsBasicMember> list = new ArrayList<FriendsBasicMember>();
		FriendsDBHelper dbhelper = new FriendsDBHelper(c);
		db = dbhelper.getReadableDatabase();

		String where = "";

		Calendar cal = Calendar.getInstance();
		String year = String.valueOf(cal.get(Calendar.YEAR));
		String month = String.valueOf(cal.get(Calendar.MONTH) + 1);
		String day = String.valueOf(cal.get(Calendar.DAY_OF_MONTH));
		if (month.length() == 1)
			month = "0" + month;
		if (day.length() == 1)
			day = "0" + day;

		if (curTab.equals(FriendsStaticVars.TAB_2)) {
			where = "date(" + FriendsDB.MEMBER_REGDATE + ") = date('" + year
					+ "-" + month + "-" + day + "')";
		} else if (curTab.equals(FriendsStaticVars.TAB_3)) {
			where = "date(" + FriendsDB.MEMBER_REGDATE
					+ ") > date('now','-7 day') OR date("
					+ FriendsDB.MEMBER_REGDATE + ") = date('" + year + "-"
					+ month + "-" + day + "')";
		} else if (curTab.equals(FriendsStaticVars.TAB_1)) {
			where = "date(" + FriendsDB.MEMBER_REGDATE
					+ ") > date('now','-3 day') OR date("
					+ FriendsDB.MEMBER_REGDATE + ") = date('" + year + "-"
					+ month + "-" + day + "')";
		} else if (curTab.equals(FriendsStaticVars.TAB_4)) {
			where = "date(" + FriendsDB.MEMBER_REGDATE
					+ ") > date('now','-30 day') OR date("
					+ FriendsDB.MEMBER_REGDATE + ") = date('" + year + "-"
					+ month + "-" + day + "')";
		}

		String[] colums = {
				FriendsDB.MEMBER_ID,
				FriendsDB.MEMBER_NAME,
				FriendsDB.MEMBER_PHOTO_ID,
				"date(strftime('%s'," + FriendsDB.MEMBER_REGDATE
						+ "),'unixepoch') as " + FriendsDB.MEMBER_REGDATE };
		String orderBy = FriendsDB.MEMBER_REGDATE + " DESC";
		Cursor cursor = db.query(FriendsDB.TABLE_MEMBER, colums, where, null,
				null, null, orderBy);

		while (cursor.moveToNext()) {
			String contactId = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_ID));
			String displayName = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_NAME));
			String photoId = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_PHOTO_ID));
			String date = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_REGDATE));

			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			Date regDate;
			try {
				regDate = format.parse(date);
			} catch (ParseException e) {
				regDate = new Date();
			}

			date = (String) DateUtils.getRelativeTimeSpanString(regDate
					.getTime());

			Uri uri = ContentUris.withAppendedId(
					ContactsContract.Contacts.CONTENT_URI, Long
							.parseLong(contactId));
			
			InputStream is = ContactsContract.Contacts
					.openContactPhotoInputStream(currentResolver, uri);

			Bitmap bitmap = BitmapFactory.decodeStream(is);

			FriendsBasicMember fbm = new FriendsBasicMember();
			fbm.set_id(contactId);
			fbm.setDisplayname(displayName);
			fbm.setPhoto(bitmap);
			fbm.setPhotoid(photoId);
			fbm.setRegdate(date);
			list.add(fbm);
		}
		cursor.close();

		for (FriendsBasicMember curMem : list) {
			String[] columsP = { FriendsDB.PHONE_NUMBER, FriendsDB.PHONE_TYPE };
			where = FriendsDB.MEMBER_ID + " = " + curMem.get_id();
			cursor = db.query(FriendsDB.TABLE_PHONE, columsP, where, null,
					null, null, null);
			ArrayList<FriendsPhone> curPhoneList = new ArrayList<FriendsPhone>();
			while (cursor.moveToNext()) {
				FriendsPhone phone = new FriendsPhone();
				phone.setMemberid(curMem.get_id());
				phone.setPhonenumber(cursor.getString(cursor
						.getColumnIndex(FriendsDB.PHONE_NUMBER)));
				phone.setPhonetype(cursor.getString(cursor
						.getColumnIndex(FriendsDB.PHONE_TYPE)));
				curPhoneList.add(phone);
			}
			cursor.close();
			curMem.setPhoneList(curPhoneList);
			if(curPhoneList.size()>0)
			curMem.setCurrentnumber(curPhoneList.get(0).getPhonenumber());
		}
		cursor.close();
		db.close();

		return list;
	}

	public ArrayList<FriendsBasicMember> getBirthList(String curTab) {
		ArrayList<FriendsBasicMember> list = new ArrayList<FriendsBasicMember>();
		FriendsDBHelper dbhelper = new FriendsDBHelper(c);
		db = dbhelper.getReadableDatabase();

		String where = "";

		Calendar cal = Calendar.getInstance();
		String year = String.valueOf(cal.get(Calendar.YEAR));
		String month = String.valueOf(cal.get(Calendar.MONTH) + 1);
		String day = String.valueOf(cal.get(Calendar.DAY_OF_MONTH));
		if (month.length() == 1)
			month = "0" + month;
		if (day.length() == 1)
			day = "0" + day;
		if (curTab.equals(FriendsStaticVars.TAB_2)) {
			// Today
			where = "strftime('%m-%d'," + FriendsDB.MEMBER_BIRTHDAY
					+ ") = strftime('%m-%d','" + month + "-" + day + "')";
		} else if (curTab.equals(FriendsStaticVars.TAB_3)) {
			// Yesterday
			where = "strftime('%m-%d'," + FriendsDB.MEMBER_BIRTHDAY
					+ ") < strftime('%m-%d','" + month + "-" + day
					+ "') AND strftime('%m-%d'," + FriendsDB.MEMBER_BIRTHDAY
					+ ") > strftime('%m-%d','" + month + "-" + day
					+ "','-2 day')";
		} else if (curTab.equals(FriendsStaticVars.TAB_4)) {
			// Tomorrow
			where = "strftime('%m-%d'," + FriendsDB.MEMBER_BIRTHDAY
					+ ") > strftime('%m-%d','" + month + "-" + day
					+ "') AND strftime('%m-%d'," + FriendsDB.MEMBER_BIRTHDAY
					+ ") < strftime('%m-%d','" + month + "-" + day
					+ "','+2 day')";
		} else {
			// +-1 day
			where = "strftime('%m-%d'," + FriendsDB.MEMBER_BIRTHDAY
					+ ") > strftime('%m-%d','" + month + "-" + day
					+ "','-2 day') AND strftime('%m-%d',"
					+ FriendsDB.MEMBER_BIRTHDAY + ") < strftime('%m-%d','"
					+ month + "-" + day + "','+2 day')";
		}

		String[] colums = {
				FriendsDB.MEMBER_ID,
				FriendsDB.MEMBER_NAME,
				FriendsDB.MEMBER_PHOTO_ID,
				"date(strftime('%s'," + FriendsDB.MEMBER_REGDATE
						+ "),'unixepoch') as " + FriendsDB.MEMBER_REGDATE };
		String orderBy = FriendsDB.MEMBER_BIRTHDAY;
		Cursor cursor = db.query(FriendsDB.TABLE_MEMBER, colums, where, null,
				null, null, orderBy);

		while (cursor.moveToNext()) {
			String contactId = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_ID));
			String displayName = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_NAME));
			String photoId = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_PHOTO_ID));
			String date = cursor.getString(cursor
					.getColumnIndex(FriendsDB.MEMBER_REGDATE));

			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			Date regDate;
			try {
				regDate = format.parse(date);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				regDate = new Date();
			}
			Date curDate = new Date();

			date = (String) DateUtils.getRelativeTimeSpanString(regDate
					.getTime(), curDate.getTime(), DateUtils.DAY_IN_MILLIS);

			Uri uri = ContentUris.withAppendedId(
					ContactsContract.Contacts.CONTENT_URI, Long
							.parseLong(contactId));
			InputStream is = ContactsContract.Contacts
					.openContactPhotoInputStream(currentResolver, uri);

			Bitmap bitmap = BitmapFactory.decodeStream(is);

			FriendsBasicMember fbm = new FriendsBasicMember();
			fbm.set_id(contactId);
			fbm.setDisplayname(displayName);
			fbm.setPhoto(bitmap);
			fbm.setPhotoid(photoId);
			// fbm.setRegdate(date);

			list.add(fbm);
		}
		cursor.close();

		for (FriendsBasicMember curMem : list) {
			String[] columsP = { FriendsDB.PHONE_NUMBER, FriendsDB.PHONE_TYPE };
			where = FriendsDB.MEMBER_ID + " = " + curMem.get_id();
			cursor = db.query(FriendsDB.TABLE_PHONE, columsP, where, null,
					null, null, null);
			ArrayList<FriendsPhone> curPhoneList = new ArrayList<FriendsPhone>();
			while (cursor.moveToNext()) {
				FriendsPhone phone = new FriendsPhone();
				phone.setMemberid(curMem.get_id());
				phone.setPhonenumber(cursor.getString(cursor
						.getColumnIndex(FriendsDB.PHONE_NUMBER)));
				phone.setPhonetype(cursor.getString(cursor
						.getColumnIndex(FriendsDB.PHONE_TYPE)));
				curPhoneList.add(phone);
			}
			cursor.close();
			curMem.setPhoneList(curPhoneList);
		}
		cursor.close();
		db.close();

		return list;
	}

	public ArrayList<FriendsBasicMember> getNewsList(String currentTab) {
		// TODO Auto-generated method stub
		return null;
	}

	public ArrayList<FriendsBasicMember> getBestList(String curTab) {
		ArrayList<FriendsBasicMember> resultList = new ArrayList<FriendsBasicMember>();
		ArrayList<FriendsBasicMember> recentlyList = new ArrayList<FriendsBasicMember>();
		ArrayList<String> callsList = new ArrayList<String>();
		ArrayList<callObj2> callsNumberList = new ArrayList<callObj2>();
		Cursor cursor = currentResolver.query(CallLog.Calls.CONTENT_URI, null,
				null, null, Calls.DEFAULT_SORT_ORDER);

		while (cursor.moveToNext()) {

			String date = cursor.getString(cursor.getColumnIndex(Calls.DATE));
			String number = cursor.getString(cursor
					.getColumnIndex(Calls.NUMBER));
			String type = cursor.getString(cursor.getColumnIndex(Calls.TYPE));

			Date regDate = new Date(Long.parseLong(date));
			Date curDate = new Date();
			Long gapToToday = (curDate.getTime() - regDate.getTime())
					/ (1000 * 60 * 60 * 24);

			date = (String) DateUtils.getRelativeTimeSpanString(Long
					.parseLong(date));

			if (!callsList.contains(number)) {
				callsList.add(number);
				callsNumberList.add(new callObj2(number, 0, type, date));
			}
		}
		cursor.close();

		ArrayList<callObj2> notInContactsList = new ArrayList<callObj2>();
		for (callObj2 curObj : callsNumberList) {
			String callstId = getMemberIdByNumber(curObj.getNumber());

			if (callstId.length() > 0) {

				String[] projection = { FriendsDB.MEMBER_ID,
						FriendsDB.MEMBER_REGDATE, FriendsDB.MEMBER_NAME };
				String selArg = FriendsDB.MEMBER_ID + " = " + callstId;

				FriendsDBHelper dbhelper = new FriendsDBHelper(c);
				db = dbhelper.getReadableDatabase();

				Cursor inCursor = db.query(FriendsDB.TABLE_MEMBER, projection,
						selArg, null, null, null, null);

				while (inCursor.moveToNext()) {
					String contactId = inCursor.getString(inCursor
							.getColumnIndex(FriendsDB.MEMBER_ID));
					String displayname = inCursor.getString(inCursor
							.getColumnIndex(FriendsDB.MEMBER_NAME));
					String regdate = curObj.getDate();

					Uri uri = ContentUris.withAppendedId(
							ContactsContract.Contacts.CONTENT_URI, Long
									.parseLong(contactId));
					InputStream is = ContactsContract.Contacts
							.openContactPhotoInputStream(currentResolver, uri);

					Bitmap bitmap = BitmapFactory.decodeStream(is);

					FriendsBasicMember fbm = new FriendsBasicMember();
					fbm.setDisplayname(displayname);
					fbm.set_id(contactId);
					fbm.setRegdate(regdate);
					fbm.setPhoto(bitmap);
					ArrayList<FriendsPhone> listPhone = new ArrayList<FriendsPhone>();
					listPhone.add(new FriendsPhone(curObj.getNumber(), curObj
							.getType()));
					fbm.setPhoneList(listPhone);
					fbm.setCurrentnumber(curObj.getNumber());
					resultList.add(fbm);

					SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
					Date regDate;
					try {
						regDate = format.parse(regdate);

					} catch (ParseException e) {
						// TODO Auto-generated catch block
						regDate = new Date();
					}

					Date curDate = new Date();
					Long gapToToday = (curDate.getTime() - regDate.getTime())
							/ (1000 * 60 * 60 * 24);

					if (gapToToday < 3) {
						recentlyList.add(fbm);
					}
				}

				inCursor.close();
				db.close();
			} else {
				notInContactsList.add(curObj);
			}
		}

		for (callObj2 curNotIn : notInContactsList) {
			int notInIndex = callsNumberList.indexOf(curNotIn);
			callsNumberList.remove(notInIndex);
			// resultList.remove(notInIndex);

		}

		String[] projection = { "count(" + Calls.NUMBER + ") as count" };

		for (callObj2 curNum : callsNumberList) {
			String selArg = Calls.NUMBER + " = ('" + curNum.getNumber() + "')";
			cursor = currentResolver.query(CallLog.Calls.CONTENT_URI,
					projection, selArg, null, null);
			while (cursor.moveToNext()) {
				int curCount = cursor.getInt(cursor.getColumnIndex("count"));
				curNum.setCount(curCount);
			}
			cursor.close();
		}

		for (int i = 0; i < callsNumberList.size() - 1; i++) {
			for (int j = i + 1; j < callsNumberList.size(); j++) {
				if (callsNumberList.get(i).getCount() < callsNumberList.get(j)
						.getCount()) {

					callObj2 curNum = new callObj2(callsNumberList.get(j)
							.getNumber(), callsNumberList.get(j).getCount(),
							callsNumberList.get(j).getType(), callsNumberList
									.get(j).getDate());
					callsNumberList.set(j, callsNumberList.get(i));
					callsNumberList.set(i, curNum);

					FriendsBasicMember curFbm = new FriendsBasicMember();
					curFbm.set_id(((FriendsBasicMember) resultList.get(j))
							.get_id());
					curFbm.setDisplayname(((FriendsBasicMember) resultList
							.get(j)).getDisplayname());
					curFbm
							.setPhoneList(((FriendsBasicMember) resultList
									.get(j)).getPhoneList());
					curFbm.setCurrentnumber(((FriendsBasicMember) resultList.get(j))
							.getCurrentnumber());
					curFbm.setPhoto(((FriendsBasicMember) resultList.get(j))
							.getPhoto());
					curFbm.setRegdate(((FriendsBasicMember) resultList.get(j))
							.getRegdate());

					resultList.set(j, resultList.get(i));
					resultList.set(i, curFbm);

				}
			}
		}

		for (int i = 0; i < resultList.size(); i++) {
			String iCount = new String(String.valueOf(callsNumberList.get(i)
					.getCount()));
			resultList.get(i).setContactCount(iCount);
		}

		if (curTab.equals(FriendsStaticVars.TAB_2)) {
			// News
			// TODO : 뉴스 가장 연락 많이 하는거 구현 ㅠ_ㅠ
		} else {
			// Call

			ArrayList<Integer> notRecentList = new ArrayList<Integer>();
			for (callObj2 curNum : callsNumberList) {
				if (curNum.getCount() == 0) {
					notRecentList.add(callsNumberList.indexOf(curNum));
				}
			}

			for (int i = notRecentList.size() - 1; i > 0; i--) {
				// Reverse Travel
				resultList.remove(notRecentList.get(i));
			}
		}

		cursor.close();

		return resultList;

	}

	private String getMemberIdByNumber(String number) {
		String[] projection = { FriendsDB.MEMBER_ID };
		String selArg = FriendsDB.PHONE_NUMBER + " = " + "'" + number + "'";

		FriendsDBHelper dbhelper = new FriendsDBHelper(c);
		SQLiteDatabase idb = dbhelper.getReadableDatabase();

		Cursor inCursor = idb.query(FriendsDB.TABLE_PHONE, projection, selArg,
				null, null, null, null);
		String curMemId = "";
		if (inCursor.moveToNext()) {
			curMemId = inCursor.getString(inCursor
					.getColumnIndex(FriendsDB.MEMBER_ID));
		}

		inCursor.close();
		idb.close();
		return curMemId;
	}
	
	private boolean isExistMemberByNumber(String number) {
		String[] projection = { "count(*) as count" };
		String selArg = FriendsDB.PHONE_NUMBER + " = " + "'" + number + "'";

		FriendsDBHelper dbhelper = new FriendsDBHelper(c);
		SQLiteDatabase idb = dbhelper.getReadableDatabase();

		Cursor inCursor = idb.query(FriendsDB.TABLE_PHONE, projection, selArg,
				null, null, null, null);
		String curMemId = "0";
		if (inCursor.moveToNext()) {
			curMemId = inCursor.getString(inCursor
					.getColumnIndex("count"));
		}
		inCursor.close();
		idb.close();
		if(curMemId.equals("0"))
			return false;
		else
			return true;
	}

	private class callObj2 {
		int count;
		String number;
		String type;
		String date;

		public callObj2() {

		}

		public callObj2(String number, int count, String type, String date) {
			this.number = number;
			this.count = count;
			this.type = type;
			this.date = date;
		}

		public String getType() {
			return type;
		}

		public void setType(String type) {
			this.type = type;
		}

		public String getDate() {
			return date;
		}

		public void setDate(String date) {
			this.date = date;
		}

		public int getCount() {
			return count;
		}

		public void setCount(int count) {
			this.count = count;
		}

		public String getNumber() {
			return number;
		}

		public void setNumber(String number) {
			this.number = number;
		}

	}

	public ArrayList<FriendsBasicMember> getRecentlyList(String curTab) {
		ArrayList<FriendsBasicMember> resultList = new ArrayList<FriendsBasicMember>();

		ArrayList<FriendsBasicMember> todayList = new ArrayList<FriendsBasicMember>();
		ArrayList<FriendsBasicMember> yesterdayList = new ArrayList<FriendsBasicMember>();

		ArrayList<String> callsList = new ArrayList<String>();
		ArrayList<callObj2> callsNumberList = new ArrayList<callObj2>();
		Cursor cursor = currentResolver.query(CallLog.Calls.CONTENT_URI, null,
				null, null, Calls.DEFAULT_SORT_ORDER);

		while (cursor.moveToNext()) {
			String date = cursor.getString(cursor.getColumnIndex(Calls.DATE));
			String number = cursor.getString(cursor
					.getColumnIndex(Calls.NUMBER));
			String type = cursor.getString(cursor.getColumnIndex(Calls.TYPE));

			if (!callsList.contains(number)) {
				callsList.add(number);
				callsNumberList.add(new callObj2(number, 0, type, date));
			}
		}
		cursor.close();

		ArrayList<callObj2> notInContactsList = new ArrayList<callObj2>();
		for (callObj2 curObj : callsNumberList) {
			String callstId = getMemberIdByNumber(curObj.getNumber());

			if (callstId.length() > 0) {

				String[] projection = { FriendsDB.MEMBER_ID,
						FriendsDB.MEMBER_REGDATE, FriendsDB.MEMBER_NAME };
				String selArg = FriendsDB.MEMBER_ID + " = " + callstId;

				FriendsDBHelper dbhelper = new FriendsDBHelper(c);
				db = dbhelper.getReadableDatabase();

				Cursor inCursor = db.query(FriendsDB.TABLE_MEMBER, projection,
						selArg, null, null, null, null);

				while (inCursor.moveToNext()) {
					String contactId = inCursor.getString(inCursor
							.getColumnIndex(FriendsDB.MEMBER_ID));
					String displayname = inCursor.getString(inCursor
							.getColumnIndex(FriendsDB.MEMBER_NAME));
					String regdate = curObj.getDate();

					Uri uri = ContentUris.withAppendedId(
							ContactsContract.Contacts.CONTENT_URI, Long
									.parseLong(contactId));
					InputStream is = ContactsContract.Contacts
							.openContactPhotoInputStream(currentResolver, uri);

					Bitmap bitmap = BitmapFactory.decodeStream(is);

					FriendsBasicMember fbm = new FriendsBasicMember();
					fbm.setDisplayname(displayname);
					fbm.set_id(contactId);
					fbm.setRegdate(DateUtils.getRelativeTimeSpanString(
							Long.parseLong(regdate)).toString());
					fbm.setPhoto(bitmap);
					ArrayList<FriendsPhone> listPhone = new ArrayList<FriendsPhone>();
					listPhone.add(new FriendsPhone(curObj.getNumber(), curObj
							.getType()));
					fbm.setPhoneList(listPhone);
					fbm.setCurrentnumber(curObj.getNumber());

					Date regDate = new Date(Long.parseLong(regdate));

					Date curDate = new Date();

					Long gapToToday = (curDate.getTime() - regDate.getTime())
							/ (1000 * 60 * 60 * 24);
					if (gapToToday == 0) {
						todayList.add(fbm);
					} else if (gapToToday == 1) {
						yesterdayList.add(fbm);
					}
					
					if(gapToToday==0 || gapToToday==1)resultList.add(fbm);

				}

				inCursor.close();
				db.close();
			} else {
				notInContactsList.add(curObj);
			}
		}

		for (callObj2 curNotIn : notInContactsList) {
			int notInIndex = callsNumberList.indexOf(curNotIn);
			callsNumberList.remove(notInIndex);
			//resultList.remove(notInIndex);
		}

		cursor.close();
		if (curTab.equals(FriendsStaticVars.TAB_2)) {
			// Today
			return todayList;
		} else if (curTab.equals(FriendsStaticVars.TAB_3)) {
			// Yesterday
			return yesterdayList;
		} else {
			// All
			return resultList;
		}

	}

	public ArrayList<FriendsBasicMember> getForgottonList(String curTab) {
		ArrayList<FriendsBasicMember> resultList = new ArrayList<FriendsBasicMember>();

		ArrayList<String> callsList = new ArrayList<String>();
		
		ArrayList<callObj2> totalList = new ArrayList<callObj2>();
		ArrayList<callObj2> twoWeeksNumberList = new ArrayList<callObj2>();
		ArrayList<callObj2> monthNumberList = new ArrayList<callObj2>();
		ArrayList<callObj2> halfNumberList = new ArrayList<callObj2>();

		Cursor cursor = currentResolver.query(CallLog.Calls.CONTENT_URI, null,
				null, null, Calls.DEFAULT_SORT_ORDER);

		while (cursor.moveToNext()) {
			String date = cursor.getString(cursor.getColumnIndex(Calls.DATE));
			String type = cursor.getString(cursor.getColumnIndex(Calls.TYPE));
			String number = cursor.getString(cursor
					.getColumnIndex(Calls.NUMBER));

			Date regDate = new Date(Long.parseLong(date));
			Date curDate = new Date();
			Long gapToToday = (curDate.getTime() - regDate.getTime())
					/ (1000 * 60 * 60 * 24);
			if (!callsList.contains(number)) {
				callsList.add(number);
				if (gapToToday > 14 && gapToToday < 30) {
					twoWeeksNumberList.add(new callObj2(number, 0, type, date));
				} if (gapToToday > 29 && gapToToday < 181) {
					monthNumberList.add(new callObj2(number, 0, type, date));
				} if (gapToToday > 180){
					halfNumberList.add(new callObj2(number, 0, type, date));
				}
			}

		}
		cursor.close();
		
		
		ArrayList<callObj2> existNumberList = new ArrayList<callObj2>();

		FriendsDBHelper dbhelper = new FriendsDBHelper(c);
		db = dbhelper.getReadableDatabase();
		String[] columns = { FriendsDB.MEMBER_ID, FriendsDB.PHONE_NUMBER };
		String where = "";
		Cursor existCursor = db.query(FriendsDB.TABLE_PHONE, columns, where,
				null, null, null, null);
		while (existCursor.moveToNext()) {
			existNumberList.add(new callObj2(existCursor.getString(existCursor
					.getColumnIndex(FriendsDB.PHONE_NUMBER)), 0, "", ""));
		}
		existCursor.close();
		db.close();
		
		for(String curContactNumber:callsList){
			Boolean existContactsRecord = false;
			int idx = 0;
			for(callObj2 cur:existNumberList){
				if(curContactNumber.equals(cur.getNumber())){
					existContactsRecord = true;
					break;
				}
				idx++;
			}
			if(existContactsRecord)existNumberList.remove(idx);
		}
		
		
		if (curTab.equals(FriendsStaticVars.TAB_2)) {
			// 2weeks
			existNumberList.clear();
			existNumberList.addAll(twoWeeksNumberList);

		} else if (curTab.equals(FriendsStaticVars.TAB_3)) {
			// monthly
			existNumberList.clear();
			existNumberList.addAll(monthNumberList);

		} else if (curTab.equals(FriendsStaticVars.TAB_4)) {
			// Half Year
			existNumberList.clear();
			existNumberList.addAll(halfNumberList);

		} else {
			// Never
		}
		
		
		for (callObj2 curObj : existNumberList) {
			String callstId = getMemberIdByNumber(curObj.getNumber());

			if (callstId.length() > 0) {

				String[] projection2 = { FriendsDB.MEMBER_ID,
						FriendsDB.MEMBER_NAME };
				String selArg = FriendsDB.MEMBER_ID + " = " + callstId;

				db = dbhelper.getReadableDatabase();

				Cursor inCursor = db.query(FriendsDB.TABLE_MEMBER, projection2,
						selArg, null, null, null, null);

				while (inCursor.moveToNext()) {
					String contactId = inCursor.getString(inCursor
							.getColumnIndex(FriendsDB.MEMBER_ID));
					String displayname = inCursor.getString(inCursor
							.getColumnIndex(FriendsDB.MEMBER_NAME));
					String regdate = curObj.getDate();

					Uri uri = ContentUris.withAppendedId(
							ContactsContract.Contacts.CONTENT_URI, Long
									.parseLong(contactId));
					InputStream is = ContactsContract.Contacts
							.openContactPhotoInputStream(currentResolver, uri);

					Bitmap bitmap = BitmapFactory.decodeStream(is);

					FriendsBasicMember fbm = new FriendsBasicMember();
					fbm.setDisplayname(displayname);
					fbm.set_id(contactId);
					if (regdate.length() > 0) {
						regdate = (String) DateUtils
								.getRelativeTimeSpanString(Long
										.parseLong(regdate));
						fbm.setRegdate(regdate);
					} else {
						fbm.setRegdate("0");
					}
					fbm.setPhoto(bitmap);
					ArrayList<FriendsPhone> listPhone = new ArrayList<FriendsPhone>();
					listPhone.add(new FriendsPhone(curObj.getNumber(), curObj
							.getType()));
					fbm.setPhoneList(listPhone);
					fbm.setCurrentnumber(curObj.getNumber());
					resultList.add(fbm);
				}

				inCursor.close();
				db.close();
			}
		}

		return resultList;
	}

}
